"""
Case Type   : PACKAGE
Case Name   : 声明package以调用用户权限执行,收回调用用户关于package的权限后
              replace已存在package
Create At   : 2022/04/26
Owner       : @zou_jialiang050
Description :
    1.声明package
    2.定义package body
    3.创建用户
    4.对用户赋予create any package权限
    5.收回新建用户关于package的权限
    6.新建用户进入数据库,切换模式至public,重新声明并定义package
    7.清理环境
Expect      :
    1.成功
    2.成功
    3.成功
    4.成功
    5.成功
    6.合理报错
    7.成功
History     :
"""

import os
import unittest
from yat.test import macro
from testcase.utils.Constant import Constant
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Logger import Logger


class PACKAGE(unittest.TestCase):
    def setUp(self):
        self.logger = Logger()
        self.primary_sh = CommonSH('PrimaryDbUser')
        self.Con = Constant()
        self.user = 'u_package_0041'
        self.table = 't_package_0041'
        self.package = 'p_test_0041'
        self.procedure = 'p_package_0041'

    def test_package(self):
        self.logger.info(f'-----{os.path.basename(__file__)} start-----')
        step = 'step1:声明package expect:成功'
        self.logger.info(step)
        spec_package = self.primary_sh.execut_db_sql(
            f'''create or replace package {self.package} is
            var1 int:=1;
            var2 int:=2;
            procedure {self.procedure}();
            end {self.package};''')
        self.logger.info(spec_package)
        self.assertIn(self.Con.create_package_success_msg, spec_package,
                      '执行失败' + step)

        step = 'step2:定义package body expect:成功'
        self.logger.info(step)
        def_package = self.primary_sh.execut_db_sql(
            f'''create or replace package body {self.package} is
            procedure {self.procedure}()
            is
            begin
                drop table if exists {self.table};
                create table {self.table}(c_int int);
                insert into {self.table} values(var1),(var2);
            end;
            end {self.package};''')
        self.logger.info(def_package)
        self.assertIn(self.Con.create_package_body_success_msg, def_package,
                      '执行失败' + step)

        step = 'step3:创建用户 expect:成功'
        self.logger.info(step)
        create_user = self.primary_sh.execut_db_sql(f'''
            drop user if exists {self.user} cascade;
            create user {self.user} with password '{macro.PASSWD_REPLACE}';''')
        self.logger.info(create_user)
        self.assertIn(self.Con.CREATE_ROLE_SUCCESS_MSG, create_user,
                      '执行失败' + step)

        step = 'step4:对用户赋予create any package权限 expect:成功'
        self.logger.info(step)
        grant_pri = self.primary_sh.execut_db_sql(f'''
            grant create any package to {self.user};''')
        self.logger.info(grant_pri)
        self.assertIn('GRANT', grant_pri, '执行失败' + step)

        step = 'step5:收回新建用户关于package的权限 expect:成功'
        self.logger.info(step)
        revoke_pri = self.primary_sh.execut_db_sql(f'''
            revoke all privileges on package {self.package} from {self.user};
            ''')
        self.logger.info(revoke_pri)
        self.assertIn('REVOKE', revoke_pri, '执行失败' + step)

        step = 'step6:新建用户进入数据库,切换模式至public,重新声明并定义' \
               'package expect:合理报错'
        self.logger.info(step)
        invoke_package = self.primary_sh.execut_db_sql(
            f'''set current_schema = public; 
            create or replace package {self.package} is
            procedure {self.procedure}();
            end {self.package};

            create or replace package body {self.package} is
            procedure {self.procedure}()
            is
            begin
                drop table if exists {self.table};
                create table {self.table}(c_text text);
                insert into {self.table} values('新用户修改');
            end;
            end {self.package};''',
            sql_type=f" -U {self.user} -W '{macro.PASSWD_REPLACE}'")
        self.logger.info(invoke_package)
        text = r'ERROR:  security definer package not ' \
            r'support replace with different owner'
        self.assertIn('SET', invoke_package, '执行失败' + step)
        self.assertIn(text, invoke_package, '执行失败' + step)

    def tearDown(self):
        step = 'step7:清理环境 expect:清理环境成功'
        self.logger.info(step)
        clean_environment = self.primary_sh.execut_db_sql(
            f'''drop package {self.package};
            drop user {self.user} cascade;''')
        self.logger.info(clean_environment)
        self.assertIn(self.Con.drop_package_success_msg, clean_environment,
                      '执行失败' + step)
        self.assertIn(self.Con.DROP_ROLE_SUCCESS_MSG, clean_environment,
                      '执行失败' + step)
        self.logger.info(f'-----{os.path.basename(__file__)} end-----')
